package com.lzx.article.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.lzx.article.entity.Articles;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * Mapper 接口
 * </p>
 *
 * @author lzx
 * @since 2023-01-11
 */
@Mapper
public interface ArticlesMapper extends BaseMapper<Articles> {

    Articles selectArticleIsDelete(Long articleId);

    Long selectArticleCount();

    @Select("SELECT MONTH\n" +
            "\t( create_time ) AS `month`,\n" +
            "\tCOUNT(*) AS `count`,\n" +
            "\t( SELECT COUNT(*) FROM articles WHERE YEAR ( create_time ) = a_year AND MONTH ( create_time ) = a_month AND DAY ( create_time ) = max_day ) AS `max_count` \n" +
            "FROM\n" +
            "\tarticles,\n" +
            "\t(\n" +
            "\tSELECT YEAR\n" +
            "\t\t( create_time ) AS a_year,\n" +
            "\t\tMONTH ( create_time ) AS a_month,\n" +
            "\t\tMAX(\n" +
            "\t\tDAY ( create_time )) AS max_day \n" +
            "\tFROM\n" +
            "\t\tarticles \n" +
            "\tWHERE\n" +
            "\t\tYEAR ( create_time ) = #{year} GROUP BY YEAR(create_time), MONTH(create_time)) b\n" +
            "\t\t\n" +
            "\tWHERE\n" +
            "\t\tYEAR ( create_time ) = #{year} AND MONTH(create_time) = b.a_month\n" +
            "\t\t\n" +
            "\tGROUP BY\n" +
            "\t\tMONTH ( create_time ),\n" +
            "\tb.a_year,\n" +
            "\tb.a_month")
    List<Map<String, Object>> countArticlesByMonthOfYear(Integer year);
}
